/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package chatserver;

import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author tiphanie
 */
public class DatabaseConnection {

    String driver = "org.apache.derby.jdbc.ClientDriver";
    String url = "jdbc:derby://localhost:1527/chat";
    String usuario = "tiph";
    String clave = "tiph";
    Connection conexion = null;
    ResultSet resultset = null;
    private static final Logger logger = Logger.getLogger(DatabaseConnection.class.getName());

    public DatabaseConnection() {
        try {
            Class.forName(driver);
            conexion = DriverManager.getConnection(url, usuario, clave);
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public boolean usernameExists(String username) {
        try {
            String query = "select * from \"USERS\" where USERNAME =?";
            PreparedStatement statement;
            statement = conexion.prepareStatement(query);
            statement.setString(1, username);
            resultset = statement.executeQuery();
            if (resultset.next()) {
                System.out.println("Already used" + resultset.getString("USERNAME"));
                return true;
            } else {
                System.out.println("free");
                return false;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al verificar si el nombre del usuario existe", ex);
            return false;
        }
    }

    public boolean createUser(User user) throws SQLException {
        try {
            String query = "insert into\"USERS\" values(?,?,?,?)";
            PreparedStatement statement;
            statement = conexion.prepareStatement(query);
            statement.setString(1, user.getRealname());
            statement.setString(2, user.getUsername());
            statement.setString(3, user.getPassword());
            statement.setString(4, user.getEmail());
            statement.execute();
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al crear un usuario", ex);
            return false;
        }
    }

    public User login(String username, String password) throws SQLException {
        try {
            String query = "select * from \"USERS\" where USERNAME = ? AND PASSWORD = ?";
            PreparedStatement statement;
            statement = conexion.prepareStatement(query);
            statement.setString(1, username);
            statement.setString(2, password);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return new User(
                        resultSet.getString("REALNAME"),
                        resultSet.getString("USERNAME"),
                        resultSet.getString("PASSWORD"),
                        resultSet.getString("EMAIL"));
            } else {
                return null;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al login", ex);
            return null;
        }
    }

    public boolean createMessage(int conversationID, String username, String message) {
        try {
            String query = "insert into\"MESSAGE\" (DATE, CONVERSATIONID, USERNAME, TEXT) values(current_timestamp,?,?,?)";
            PreparedStatement statement;
            statement = conexion.prepareStatement(query);
            statement.setInt(1, conversationID);
            statement.setString(2, username);
            statement.setString(3, message);
            statement.execute();
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al crear a message", ex);
            return false;
        }
    }

    public int createConversation() {
        try {
            String query = "INSERT INTO TIPH.CONVERSATION (DATE) VALUES (current_timestamp)";
            PreparedStatement statement;
            statement = conexion.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            statement.execute();
            ResultSet result = statement.getGeneratedKeys();
            if( result.next() )
                 return result.getInt(1);
            else
                 return -1;

        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error a crear al conversation", ex);
            return -1;
        }
    }

    public boolean createUserInConversation(String username, int id) {
        try {
            String query = "select * from \"USERSINCONVERSATION\" where username = ? and conversationid = ?";
            PreparedStatement statement;
            statement = conexion.prepareStatement(query);
            statement.setString(1, username);
            statement.setInt(2, id);
            statement.execute();
            if( !statement.getResultSet().next() ) { // if there is no such line
                query = "insert into\"USERSINCONVERSATION\" values(?,?)";
                statement = conexion.prepareStatement(query);
                statement.setString(1, username);
                statement.setInt(2, id);
                statement.execute();
            }
            return true;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al crear UserInConversation", ex);
            return false;
        }
    }

    public ArrayList<HistoricConversation> getConversationList(String username) {
        try {
            String query = "select c.DATE, c.ID, users.USERNAME from CONVERSATION as c,"
            + " USERSINCONVERSATION as uc, USERSINCONVERSATION as users "
            + "where c.id = uc.CONVERSATIONID and c.id = users.CONVERSATIONID and uc.username = ?"
            + " order by c.DATE, c.ID";
            PreparedStatement statement;
            statement = conexion.prepareStatement(query);
            statement.setString(1,username);
            ResultSet resultSet = statement.executeQuery();
            ArrayList<HistoricConversation> historic = new ArrayList<HistoricConversation>();
            
            int lastConvId = -1;
            HistoricConversation lastHistConv = null;
            
            while(resultSet.next()) {
                int convId = resultSet.getInt("ID");
                if( lastConvId != convId ) {
                    lastHistConv = new HistoricConversation(convId,resultSet.getString("DATE"));
                    lastConvId = convId;
                    historic.add(lastHistConv);
                }
                
                lastHistConv.usernames.add(resultSet.getString("USERNAME"));
            }
            
            return historic;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al recuperar el historico", ex);
            return null;
        }

    }
    
    public ArrayList<Message> getHistory(String username, int conversationid) {
        try {
            String query = "select m.DATE, m.username, m.TEXT, m.conversationid "
                    + "from usersinconversation as uc, MESSAGE as m "
                    + "where uc.CONVERSATIONID = ? and uc.CONVERSATIONID = m.CONVERSATIONID and uc.USERNAME = ? "
                    + "order by m.date asc";
            PreparedStatement statement;
            statement = conexion.prepareStatement(query);
            statement.setInt(1,conversationid);
            statement.setString(2,username);
            ResultSet resultSet = statement.executeQuery();
            ArrayList<Message> messages = new ArrayList<Message>();
            while(resultSet.next()) {
                messages.add(new Message(
                        resultSet.getString("DATE"),
                        resultSet.getInt("CONVERSATIONID"),
                        resultSet.getString("USERNAME"),
                        resultSet.getString("TEXT")));
            }
            return messages;
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error al recuperar el historico", ex);
            return null;
        }

    }
}